package com.infore.dataReceiving.repository;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.dataReceiving.entity.HourData;

@Repository
public interface IHourDataRepository extends JpaRepository<HourData, String>, JpaSpecificationExecutor<HourData>  {


	public List<HourData> findByDataTimeBetweenOrderByDataTimeDesc(Timestamp beginTime,Timestamp endTime);
	
	public List<HourData> findByDataTimeAndMn(Date date, String mn);
	
	public List<HourData> findByMnInAndDataTimeBetweenOrderByDataTimeDesc(List<String> stationList,Timestamp beginTime,Timestamp endTime);
	
	public List<HourData> findByMnAndFactorCodeAndDataTimeBetweenOrderByDataTimeDesc(String mn,String factorCode,
	        Timestamp beginTime,Timestamp endTime);
    
	public List<HourData> findByMnInAndFactorCodeInAndDataTimeBetweenOrderByDataTimeAsc(List<String> stationCode,List<String>factorCode,Timestamp beginTime,Timestamp endTime);
	
	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select * from ("+
			"select rownum no,c.mn,c.dataTime,c.w01010,c.w01001,c.w01009,c.w01003,c.w01014,c.w01019,c.w21003,c.w21011,c.w21001,c.w01018,c.w01016 " + 
			"from " + 
			"(select mn,data_time dataTime, " + 
			"avg(case when factor_code='w01010' then avg_value else null end) w01010," + 
			"avg(case when factor_code='w01001' then avg_value else null end) w01001," + 
			"avg(case when factor_code='w01009' then avg_value else null end) w01009," + 
			"avg(case when factor_code='w01003' then avg_value else null end) w01003," + 
			"avg(case when factor_code='w01014' then avg_value else null end) w01014," + 
			"avg(case when factor_code='w01019' then avg_value else null end) w01019," + 
			"avg(case when factor_code='w21003' then avg_value else null end) w21003," + 
			"avg(case when factor_code='w21011' then avg_value else null end) w21011," + 
			"avg(case when factor_code='w21001' then avg_value else null end) w21001," + 
			"avg(case when factor_code='w01018' then avg_value else null end) w01018, " + 
			"avg(case when factor_code='w01016' then avg_value else null end) w01016 " + 
			"from hour_data where mn in (:stationList) and data_time between to_date(:beginTime,'yyyy-MM-dd HH24:mi:ss') and  "
			+ "to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "group by mn,data_time order by data_time desc) c " + 
			"where rownum<=:endSize"+
			" ) table_alias WHERE table_alias.no >=:startSize")
	public List<Map<String, Object>> findHourData(List<String> stationList,String beginTime,String endTime,int endSize,int startSize);
	
	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select * from ("+
			"select rownum no,c.mn,c.dataTime,c.w01010,c.w01001,c.w01009,c.w01003,c.w01014,c.w01019,c.w21003,c.w21011,c.w21001,c.w01018,c.w01016 " + 
			"from(select row_number() over (partition by r.mn order by r.mn desc) rn, "
			+ "r.mn,r.dataTime,r.w01010,r.w01001,r.w01009,r.w01003,r.w01014,r.w01019,r.w21003,r.w21011,r.w21001,r.w01018,r.w01016 from " + 
			"(select mn,data_time dataTime, " + 
			"avg(case when factor_code='w01010' then avg_value else null end) w01010," + 
			"avg(case when factor_code='w01001' then avg_value else null end) w01001," + 
			"avg(case when factor_code='w01009' then avg_value else null end) w01009," + 
			"avg(case when factor_code='w01003' then avg_value else null end) w01003," + 
			"avg(case when factor_code='w01014' then avg_value else null end) w01014," + 
			"avg(case when factor_code='w01019' then avg_value else null end) w01019," + 
			"avg(case when factor_code='w21003' then avg_value else null end) w21003," + 
			"avg(case when factor_code='w21011' then avg_value else null end) w21011," + 
			"avg(case when factor_code='w21001' then avg_value else null end) w21001, " + 
			"avg(case when factor_code='w01018' then avg_value else null end) w01018, " + 
			"avg(case when factor_code='w01016' then avg_value else null end) w01016 " + 
			"from hour_data where mn in (:stationList) group by mn,data_time order by data_time desc) r) c " + 
			"where c.rn = 1 and rownum<=:endSize"+
			" ) table_alias WHERE table_alias.no >=:startSize")
	public List<Map<String, Object>> findRealTimeData(List<String> stationList,int endSize,int startSize);
	
	
	@Query(nativeQuery=true,value="select * from ("+
            "select rownum no,c.mn,c.dataTime,c.w01010,c.w01001,c.w01009,c.w01003,c.w01014,c.w01019,c.w21003,c.w21011,c.w21001,c.w01018,c.w01016 " + 
            "from(select row_number() over (partition by r.mn order by r.mn desc) rn, "
            + "r.mn,r.dataTime,r.w01010,r.w01001,r.w01009,r.w01003,r.w01014,r.w01019,r.w21003,r.w21011,r.w21001,r.w01018,r.w01016 from " + 
            "(select mn,data_time dataTime,"
            + "sum(decode(t.factor_code, 'w01010', avg_value,null)) as w01010," + 
            "  sum(decode(t.factor_code, 'w01001', avg_value,null)) as w01001," + 
            "  sum(decode(t.factor_code, 'w01009', avg_value,null)) as w01009," + 
            "  sum(decode(t.factor_code, 'w01003', avg_value,null)) as w01003," + 
            "  sum(decode(t.factor_code, 'w01014', avg_value,null)) as w01014," + 
            "  sum(decode(t.factor_code, 'w01019', avg_value,null)) as w01019," + 
            "  sum(decode(t.factor_code, 'w21003', avg_value,null)) as w21003," + 
            "  sum(decode(t.factor_code, 'w21011', avg_value,null)) as w21011," + 
            "  sum(decode(t.factor_code, 'w21001', avg_value,null)) as w21001," + 
            "  sum(decode(t.factor_code, 'w01018', avg_value,null)) as w01018," + 
            "  sum(decode(t.factor_code, 'w01016', avg_value,null)) as w01016 " + 
            "from hour_data t where "
            + "t.DATA_TIME BETWEEN :startTime and :endTime and"
            + " t.mn in (:stationList) group by mn,data_time order by data_time desc) r) c " + 
            "where c.rn = 1 and rownum<=:endSize"+
            " ) table_alias WHERE table_alias.no >=:startSize")
    public List<Map<String, Object>> findRealTimeDataYCS(Date startTime, Date endTime, List<String> stationList,int endSize,int startSize);
	
	
	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select count(*) sl from("+
			"select count(*) sl " + 
			"from hour_data where mn in (:stationList) and data_time between to_date(:beginTime,'yyyy-MM-dd HH24:mi:ss') and  "
			+ "to_date(:endTime,'yyyy-MM-dd HH24:mi:ss') "
			+ "group by mn,data_time order by data_time desc) aa")
	public Integer findHourDataCount(List<String> stationList,String beginTime,String endTime);
	
	

	/**
	 * @param stationList
	 * @return
	 */
	@Query(nativeQuery=true,value="select count(*) sl from"+
			"(select row_number() over (partition by r.mn order by r.mn desc) rn from " + 
			"(select mn from hour_data where mn in (:stationList) group by mn,data_time order by data_time desc) r) c " + 
			"where c.rn = 1")
	public Integer findRealTimeDataCount(List<String> stationList);
	
	
	/** 查询区域下的所有站点平均因子数据 历史小时数据 **/
	@Query(nativeQuery=true,value="SELECT TO_CHAR(DATA_TIME,'yyyy-mm-dd hh24')DTIME,FACTOR_CODE,AVG(AVG_VALUE) AVALUE from HOUR_DATA where DATA_TIME BETWEEN TO_DATE(:beginTime, 'yyyy-mm-dd hh24:mi:ss')\r\n" + 
	        " and TO_DATE(:endTime, 'yyyy-mm-dd hh24:mi:ss')\r\n" + 
	        "GROUP BY DATA_TIME,FACTOR_CODE ORDER BY DATA_TIME,FACTOR_CODE asc")
	public List<Map<String, Object>> queryAreaDataHourData(String beginTime,String endTime);
	
	
	/**通过站点，因子，时间段，得到每一条记录,返回listMap---用于单点自动站报表**/
	@Query(value = "select to_char(t.data_time,'yyyy-mm-dd hh24:mi:ss') as TIME ,t.mn as MN,t.factor_code as CODE,t.avg_value as VALUE,t.flag from HOUR_DATA t where (t.mn in (:mnList) ) and ( t.factor_code in (:projectCodes) ) and (t.data_time between  (to_date(:startTime,'yyyy-mm-dd hh24:mi:ss')) and (to_date(:endTime,'yyyy-mm-dd hh24:mi:ss'))) order by t.data_time asc",nativeQuery = true)
	List<Map<String, Object>> audioSingReport(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);
	
	
	/**通过站点，因子，时间段，数据总数，有效数据总数，得到每一条记录,返回listMap---用于多点自动站报表**/
	@Query(value = "select t.mn as MN, t.factor_code as CODE,to_char(avg(t.avg_value),'fm999999990.999999999') "
			+ "as VALUE,to_char(count(*)) as COUNT,sum (CASE t.flag WHEN 'N' THEN 1 ELSE 0 END) as FLAGS_N,"
			+ "count (CASE flag WHEN 'N' THEN 1 ELSE 0 END) as FLAG from HOUR_DATA t " + 
			" where (t.mn in (:mnList)) and (t.factor_code in (:projectCodes)) and (t.data_time between (to_date(:startTime, 'yyyy-mm-dd hh24:mi:ss')) and  (to_date(:endTime, 'yyyy-mm-dd hh24:mi:ss'))) " + 
			" group by t.mn,t.factor_code " + 
			" order by t.mn asc",nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("startTime") String startTime,@Param("endTime") String endTime,
			@Param("projectCodes") List<String> projectCodes,@Param("mnList") List<String> mnList);
	
	/**************** 统计所需接口 **************************/
	/**
	* @Title: getUnStatisticsDataTime
	* @Description: 获取未统计的日时间集合
	* @return    参数
	* @return List<Map<String,Object>>    返回类型
	* @throws
	 */
    @Query(value = "SELECT TO_CHAR(DATA_TIME,'yyyy-MM-dd') TIME FROM HOUR_DATA " + 
                " WHERE STATISTICS_FLAG = 0 GROUP BY TO_CHAR(DATA_TIME,'yyyy-MM-dd')",nativeQuery = true)
    List<Map<String, Object>> getUnStatisticsDayDataTime();
    
    /**
     * @Title: getUnStatisticsWeekDataTime
     * @Description: 获取未统计的周时间集合
     * @return    参数
     * @return List<Map<String,Object>>    返回类型
     * @throws
      */
     @Query(nativeQuery = true,value = "SELECT TO_CHAR(DATA_TIME,'YYYY') YEAR,TO_CHAR(DATA_TIME,'iw') WEEK "
             + "FROM HOUR_DATA WHERE STATISTICS_FLAG = 0 GROUP BY TO_CHAR(DATA_TIME,'YYYY'),TO_CHAR(DATA_TIME,'iw') ")
     List<Map<String, Object>> getUnStatisticsWeekDataTime();
    
    /**
     * @Title: getUnStatisticsMonthDataTime
     * @Description: 获取未统计的月时间集合
     * @return    参数
     * @return List<Map<String,Object>>    返回类型
     * @throws
      */
     @Query(value = "SELECT TO_CHAR(DATA_TIME,'yyyy-MM') TIME FROM HOUR_DATA " + 
                 " WHERE STATISTICS_FLAG = 0 GROUP BY TO_CHAR(DATA_TIME,'yyyy-MM')",nativeQuery = true)
     List<Map<String, Object>> getUnStatisticsMonthDataTime();
     
     /**
      * @Title: getUnStatisticsQuarterDataTime
      * @Description: 获取未统计的季度时间集合
      * @return    参数
      * @return List<Map<String,Object>>    返回类型
      * @throws
       */
      @Query(value = "SELECT TO_CHAR(DATA_TIME,'yyyy') YEAR,TO_CHAR(DATA_TIME,'Q') QUARTER FROM HOUR_DATA " + 
                  " WHERE STATISTICS_FLAG = 0 GROUP BY TO_CHAR(DATA_TIME,'yyyy'),TO_CHAR(DATA_TIME,'Q')",nativeQuery = true)
      List<Map<String, Object>> getUnStatisticsQuarterDataTime();
      
      /**
       * @Title: getUnStatisticsYearDataTime
       * @Description: 获取未统计的年时间集合
       * @return    参数
       * @return List<Map<String,Object>>    返回类型
       * @throws
        */
       @Query(value = "SELECT TO_CHAR(DATA_TIME,'yyyy') TIME FROM HOUR_DATA " + 
                   " WHERE STATISTICS_FLAG = 0 GROUP BY TO_CHAR(DATA_TIME,'yyyy')",nativeQuery = true)
       List<Map<String, Object>> getUnStatisticsYearDataTime();
    
    /**
     * @Title: getCountDayData
     * @Description: 查询小时表,统计各站的日数据
     * @param dayTimeList
     * @return    参数
     * @return List<Map<String,Object>>    返回类型
     * @throws
      */
     @Query(nativeQuery = true,value = "SELECT MN,FACTOR_CODE," + 
             "      TO_CHAR(DATA_TIME,'yyyy-MM-dd') TIME,AVG(AVG_VALUE) DATA_VALUE_AVG," + 
             "      MAX(AVG_VALUE) DATA_VALUE_MAX,MIN(AVG_VALUE) DATA_VALUE_MIN,SUM(AVG_VALUE) TOTAL_VALUE,COUNT(1) REAL_NUM " + 
             "      FROM HOUR_DATA WHERE TO_CHAR(DATA_TIME,'yyyy-MM-dd') IN (:dayTimeList) " + 
             "GROUP BY MN,FACTOR_CODE,TO_CHAR(DATA_TIME,'yyyy-MM-dd') ")
     public List<Map<String, Object>> getCountDayData(@Param("dayTimeList")List<String> dayTimeList);
     
     /**
      * @Title: updateHourDayData
      * @Description: 更新已统计后的日表小时数据标识
      * @param flag
      * @return    参数
      * @return int    返回类型
      * @throws
       */
      @Modifying
      @Transactional 
      @Query(nativeQuery = true,value = "UPDATE HOUR_DATA SET STATISTICS_FLAG = :flag WHERE STATISTICS_FLAG = 0 ")
      int updateHourDayData(@Param("flag")Integer flag);
      
      @Query(nativeQuery = true,value = "SELECT h.mn, count(DISTINCT h.data_time) NUM from hour_data h where h.MN IN ?1 " + 
              "and h.data_time BETWEEN ?2   AND ?3 " + 
              "GROUP BY h.mn order by  h.mn")
      public List<Map<String,Object>> queryStatisMnsHourDataNum(List<String> stationList,Timestamp beginTime,Timestamp endTime);
      
      @Query(nativeQuery = true,value = "SELECT h.mn,h.data_time from hour_data h where h.MN = ?1 " + 
              "and h.data_time BETWEEN ?2   AND ?3 " + 
              "GROUP BY h.mn,h.data_time order by h.mn,h.data_time")
      public List<Map<String,Object>> queryStatisMnsHourData(String mn,Timestamp beginTime,Timestamp endTime);
      
      

}
